package com.itm.lqm.dao;

import java.sql.Connection;


import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.itm.lqm.entity.Course;
import com.itm.lqm.util.BaseDao;


public class CourseDao {
	
	//查询课程
		public List<Course> findCourseAll(){
			try{
				List<Course> list=new ArrayList<Course>();
			Connection conn=BaseDao.getConnection();
			PreparedStatement ps=conn.prepareStatement("select cid,cno,cname,tname,taddress FROM courses,teacher WHERE courses.tid=teacher.tid");
			 ResultSet rs=ps.executeQuery();
			 Course c=null;
			 while(rs.next()){
				 c=new Course();
				 c.setCid(rs.getInt("cid"));
				 c.setCno(rs.getString("cno"));
				 c.setCname(rs.getString("cname"));
				 c.setTname(rs.getString("tname"));
				 c.setTaddress(rs.getString("taddress"));
			     list.add(c);
				 System.out.println(c);
			 }
			 return list;
			 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
			
		}
		
		
	
	/*
	public List<Course> findCourseAll(){
		try{
			List<Course> list=new ArrayList<Course>();
		Connection conn=BaseDao.getConnection();
		PreparedStatement ps=conn.prepareStatement("select cid,cno,cname,tname,taddress FROM courses,teacher");
		 ResultSet rs=ps.executeQuery();
		 Course c=null;
		 while(rs.next()){
			 c=new Course();
			 c.setCid(rs.getInt("cid"));
			 c.setCno(rs.getString("cno"));
			 c.setCname(rs.getString("cname"));
			 c.setTname(rs.getString("tname"));
			 c.setTaddress(rs.getString("taddress"));
		     list.add(c);
			// System.out.println(c);
		 }
		 return list;
		 
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
		return null;
	}
		
	}
	*/
	
	
	
		//添加课程
		public boolean addCourse(Course c){
			try{
				Connection con=BaseDao.getConnection();
				PreparedStatement pstm=con.prepareStatement("insert into courses(cid,cno,cname,sid,tid,taddress) values(?,?,?,?,?,?)");
				
				pstm.setInt(1, c.getCid());
				pstm.setString(2, c.getCno());
				pstm.setString(3, c.getCname());
				pstm.setInt(4,c.getSid());
				pstm.setInt(5,c.getTid());
				pstm.setString(6,c.getTaddress());
				int rs = pstm.executeUpdate();
				if(rs>0){
					return true;
				}else{
					return false;
				}
			}catch(Exception e){
				e.printStackTrace();
				return false;
			}
		}
		//修改课程
		public boolean updateCourse(Course c){
			try{
				Connection con=BaseDao.getConnection();
				PreparedStatement pstm=con.prepareStatement("update courses set cid=?,cno=?,cname=?,tid=?,taddress=? where cid=?");
				pstm.setInt(1, c.getCid());
				pstm.setString(2, c.getCno());
				pstm.setString(3, c.getCname());
				pstm.setInt(4,c.getTid());
				pstm.setString(5,c.getTaddress());
				pstm.setInt(6, c.getCid());
	            int rs = pstm.executeUpdate();
	            System.out.println(rs);
	            if(rs>0){
					return true;
				}else{
					return false;
				}
			}catch(Exception e){
				e.printStackTrace();
				return false;
			}
		}
		//删除课程
		public boolean deleteCourseBycid(int cid){
			try{
				Connection con=BaseDao.getConnection();
				PreparedStatement pstm=con.prepareStatement("delete from courses where cid=?");
				pstm.setInt(1, cid);
				int rs=pstm.executeUpdate();
				if(rs>0){
					return true;
				}else{
					return false;
				}
			}catch(Exception e){
				e.printStackTrace();
				return false;
			}
		}
		
		
		
		public Course findCourseByTid(int cid){
			try{
				Connection con=BaseDao.getConnection();
				//select cid,cno,cname,tname,taddress from courses,teacher where courses.tid=teacher.tid and cid=?
				PreparedStatement pstm=con.prepareStatement("select cid,cno,cname,tid,taddress from courses where cid=?");
				//pstm.set(1, book_id);
				pstm.setInt(1, cid);
				ResultSet rs=pstm.executeQuery();
				Course c=null;
				while(rs.next()){
					c=new Course();
					 c.setCid(rs.getInt("cid"));
					 c.setCno(rs.getString("cno"));
					 c.setCname(rs.getString("cname"));
					 c.setTid(rs.getInt("tid"));
//					 c.setSid(rs.getInt("sid"));
					// c.setTname(rs.getString("tname"));
					 c.setTaddress(rs.getString("taddress"));
					
					
				}
				return c;
			}catch(Exception e){
				e.printStackTrace();
				return null;
			}
		}

		
	
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
	/*	
		public Integer findCourseByTid(String tid){
			try{
				Connection con=BaseDao.getConnection();
				PreparedStatement pstm=con.prepareStatement("select tname from teacher where tid=?");
				pstm.setString(1, tid);
				ResultSet rs=pstm.executeQuery();
				
				int id=rs.getInt("tid");
				System.out.println("aaaaaaaa"+id);
				return id;
			}catch(Exception e){
				e.printStackTrace();
				return null;
			}
		}*/
		
		
		
		
		
		
		
		
		
		
		/*
		 * 验证该商品是否存在<根据name>
		 */
//		public boolean isExist(String name){
//			return findBookByName(name)==null?false:true;
//		}
		/*
		 * 根据id查询相应商品
		 */
		public Course findCourseByCid(int cid){
			try{
				Connection con=BaseDao.getConnection();
				//select cid,cno,cname,tname,taddress from courses,teacher where courses.tid=teacher.tid and cid=?
				PreparedStatement pstm=con.prepareStatement("select * from courses where cid=?");
				//pstm.set(1, book_id);
				pstm.setInt(1, cid);
				ResultSet rs=pstm.executeQuery();
				Course c=null;
				while(rs.next()){
					c=new Course();
					 c.setCid(rs.getInt("cid"));
					 c.setCno(rs.getString("cno"));
					 c.setCname(rs.getString("cname"));
					 c.setTid(rs.getInt("tid"));
					 c.setSid(rs.getInt("sid"));
					// c.setTname(rs.getString("tname"));
					 c.setTaddress(rs.getString("taddress"));
					
					
				}
				return c;
			}catch(Exception e){
				e.printStackTrace();
				return null;
			}
		}
		/*
		 * 根据name查询相应商品
		 */
		/*public Book findBookByName(String name){
			try{
				Connection con=BaseDao.getConnection();
				PreparedStatement pstm=con.prepareStatement("select * from book where book_name=?");
				pstm.setString(1, name);
				ResultSet rs=pstm.executeQuery();
				Book b=null;
				while(rs.next()){
					b=new Book();
					b.setBook_id(rs.getString(1));
					b.setBook_name(rs.getString(2));
					b.setAuthor(rs.getString(3));
					b.setPress(rs.getString(4));
				}
				return b;
			}catch(Exception e){
				e.printStackTrace();
				return null;
			}
		}*/
		/*
		 * 验证该商品是否存在<根据id>
		 */
		public boolean isExistCid(int cid){
			return findCourseByCid(cid)==null?false:true;
		}
		/*
		public boolean isExistTid(String tname){
			return findCourseByTid(tname)==null?false:true;
		}*/
}
